use std::collections::HashMap;

use crate::base::get_config_path;
use crate::page_conf::import_props::ImportProps;

use crate::prelude::{ get_my_pool, Column, Error, ImportMode, Result };
use crate::prelude::{ Callback, MysqlDatabase };

use calamine::{ open_workbook_auto, open_workbook_auto_from_rs, Data, DataType, Range, Reader };
use serde_json::{ Map, Value };
use std::io::Cursor;

use super::{ column_name_to_number, convert, json_val_to_string };

pub async fn import_by_excel(
    conf_name: &str,
    paras_vals: HashMap<String, String>,
    excel_reader: &ExcelReader,
    callback: Option<Box<dyn Callback>>
) -> Result<u64> {
    let conf = crate::prelude
        ::get_page_config(conf_name)
        .or_else(|e| Err(Error::Conf(e.to_string())))?;
    let imp_conf = conf.import_props.as_ref().unwrap();
    let clen = imp_conf.col_map.len();
    let mut has_data = false;
    for (i, data) in excel_reader.sheet_data.rows().enumerate() {
        if i + 1 == imp_conf.start_row {
            if data.len() < clen {
                return Err(Error::from("excel列数少于配置列数"));
            }
            has_data = true;
        }
    }
    if !has_data {
        return Err(Error::from("没有要导入的数据"));
    }
    let tx = get_my_pool().begin().await?;
    let mut mydb = MysqlDatabase::from_with_tx(conf_name, callback, tx)?;
    if imp_conf.import_mode == ImportMode::DeleteAfterInsert {
        mydb.delete_by_map(&paras_vals).await?;
    }
    let leafs = conf.get_leaf_columns();
    let count = excel_reader.import_json_vals(&leafs, &paras_vals, &imp_conf, &mut mydb).await?;
    mydb.commit().await?;
    Ok(count as u64)
}
pub struct ExcelReader {
    sheet_data: Range<Data>,
}
impl ExcelReader {
    pub fn from_file_name(excel_file_name: &str) -> Result<Self> {
        let file_path = format!("{}/upload_files/{}", get_config_path(), excel_file_name);
        let work_book = open_workbook_auto(&file_path)?;
        let sheet_data = (match work_book {
            calamine::Sheets::Xls(mut f) => {
                let sheet_data = f
                    .worksheet_range_at(0)
                    .ok_or_else(|| Error::from("没有找到sheet1"))??;
                Ok(sheet_data)
            }
            calamine::Sheets::Xlsx(mut f) => {
                let sheet_data = f
                    .worksheet_range_at(0)
                    .ok_or_else(|| Error::from("没有找到sheet1"))??;
                Ok(sheet_data)
            }
            _ => {
                let msg = format!("文件的类型无效,文件的扩展名必须是xlsx或者xls文件");
                Err(Error::from(msg))
            }
        })?;
        std::fs::remove_file(file_path)?;
        Ok(Self { sheet_data })
    }
    pub fn from_bytes(bytes: &[u8]) -> Result<Self> {
        let cursor = Cursor::new(bytes);
        let work_book = open_workbook_auto_from_rs(cursor)?;
        let sheet_data = (match work_book {
            calamine::Sheets::Xls(mut f) => {
                let sheet_data = f
                    .worksheet_range_at(0)
                    .ok_or_else(|| Error::from("没有找到sheet1"))??;
                Ok(sheet_data)
            }
            calamine::Sheets::Xlsx(mut f) => {
                let sheet_data = f
                    .worksheet_range_at(0)
                    .ok_or_else(|| Error::from("没有找到sheet1"))??;
                Ok(sheet_data)
            }
            _ => {
                let msg = format!("文件的类型无效,文件的扩展名必须是xlsx或者xls文件");
                Err(Error::from(msg))
            }
        })?;
        Ok(Self { sheet_data })
    }
}
impl ExcelReader {
    pub async fn get_cell_value(&self, row: u32, col: u32) -> Result<Data> {
        if let Some(cell) = self.sheet_data.get_value((row, col)) {
            Ok(cell.clone())
        } else {
            Err(Error::from(format!("没有找到指定的单元格数据:({},{})", row, col)))
        }
    }
    fn is_empty_row(&self, fcols: &Vec<&Column>, data: &[Data], imp_conf: &ImportProps) -> bool {
        for c in fcols.iter() {
            let name = c.name.as_ref().unwrap();
            let excel_col = imp_conf.col_map.get(name).map(|x| x.as_str());
            let idx = excel_col.map(|col| (column_name_to_number(&col).unwrap() as usize) - 1);
            let is_empty = if let Some(idx) = idx {
                if let Some(val) = data.get(idx) {
                    match val {
                        Data::Empty => true,
                        Data::String(s) => s.is_empty(),
                        Data::Float(f) => f.is_nan() || *f == 0.0,
                        Data::Int(i) => *i == 0,
                        _ => false,
                    }
                } else {
                    true
                }
            } else {
                false
            };
            if imp_conf.skip_empty_cols.contains(name) {
                if !is_empty {
                    return false;
                }
            }
        }
        true
    }
    async fn import_json_vals<'a>(
        &self,
        fcols: &Vec<&Column>,
        paras_vals: &'a HashMap<String, String>,
        imp_conf: &'a ImportProps,
        mydb: &mut MysqlDatabase
    ) -> Result<usize> {
        let datas = &self.sheet_data;
        let mut count: usize = 0;
        let mut err_msgs: Vec<String> = Vec::new();
        for (i, data) in datas.rows().enumerate() {
            if i + 1 < imp_conf.start_row {
                continue;
            }
            count += 1;
            if err_msgs.len() == 10 {
                err_msgs.push("太多错误了...".to_owned());
                break;
            }
            let is_empty = self.is_empty_row(fcols, data, &imp_conf);
            if is_empty {
                continue;
            }
            let json_vals = find_json_by_uniqe_keys(paras_vals, fcols, imp_conf, data, mydb).await;
            if let Err(err) = json_vals {
                let msg = format!("第{}行:{}", i + 1, err.to_string());
                err_msgs.push(msg);
                continue;
            }
            let mut json_vals = json_vals.unwrap();
            let mut json_ok = true;
            let mut is_new: bool = false;
            for (j, c) in fcols.iter().enumerate() {
                let name = c.name.as_deref().unwrap();
                if c.is_pk {
                    if !json_vals.contains_key(name) {
                        json_vals.insert(name.to_string(), (0u64).into());
                        is_new = true;
                    }
                    continue;
                }
                let excel_col = imp_conf.col_map.get(name).map(|x| x.as_str());
                let val = get_json_val(data, paras_vals, excel_col, &fcols[j]).await;
                if let Err(err) = val {
                    let msg = format!("第{}行:{}", i + 1, err.to_string());
                    err_msgs.push(msg);
                    json_ok = false;
                    continue;
                }
                let val = val.unwrap();
                if json_vals.contains_key(name) {
                    json_vals[name] = val;
                } else {
                    json_vals.insert(name.to_owned(), val);
                }
            }
            if !json_ok {
                continue;
            }
            let res = if is_new {
                mydb.create_by_json(json_vals.into()).await
            } else {
                mydb.update_by_json(json_vals.into()).await
            };
            if let Err(err) = res {
                let msg = format!("第{}行:{}", i + 1, err.to_string());
                err_msgs.push(msg);
            }
        }
        if !err_msgs.is_empty() {
            let msg = format!("@{}", err_msgs.join("|"));
            return Err(Error::from(msg));
        }
        Ok(count)
    }
}

async fn find_json_by_uniqe_keys(
    paras_vals: &HashMap<String, String>,
    leafs: &Vec<&Column>,
    imp_conf: &ImportProps,
    data: &[Data],
    mydb: &mut MysqlDatabase
) -> Result<Map<String, Value>> {
    if imp_conf.import_mode != ImportMode::InsertAndUpdate {
        return Ok(Map::new());
    }
    let mut pvals: HashMap<String, String> = HashMap::new();
    for (i, c) in leafs.iter().enumerate() {
        let name = c.name.as_deref().unwrap();
        let is_in = imp_conf.unique_keys.iter().any(|k| k == name);
        if is_in {
            let excel_col = imp_conf.col_map.get(name).map(|x| x.as_str());
            let val = get_json_val(data, paras_vals, excel_col, &leafs[i]).await?;
            pvals.insert(name.to_string(), json_val_to_string(&val));
        }
    }
    let val_opt = mydb.find_json_by_uniqe_keys(pvals).await?;
    let json_vals = if let Some(val) = val_opt { val } else { Map::new() };
    Ok(json_vals)
}
async fn get_json_val(
    data: &[Data],
    paras_vals: &HashMap<String, String>,
    excel_col: Option<&str>,
    fcol: &Column
) -> Result<Value> {
    let name = fcol.name.as_deref().unwrap();
    if let Some(col) = excel_col {
        let idx = (column_name_to_number(&col)? as usize) - 1;
        if let Some(val) = data.get(idx) {
            if let Some(opts) = fcol.edit_props.as_ref().and_then(|ep| ep.options.as_ref()) {
                if val.is_empty() {
                    if let Some(eps) = fcol.edit_props.as_ref() {
                        if let Some(dv) = eps.init_val.as_ref() {
                            let dv = dv.get_value().await?;
                            let iv: u64 = dv
                                .parse()
                                .or_else(|_| {
                                    Err(Error::from(format!("初始值{}不是数字类型", dv)))
                                })?;
                            return Ok(iv.into());
                        }
                    }
                    if fcol.is_null {
                        return Ok(Value::Null);
                    } else {
                        return Err(Error::from(format!("{}不能为空,请检查修改", name)));
                    }
                }
                let sval = val.to_string();
                let val_opt = opts.get_key_by_label(sval.as_str()).await?;
                if let Some(id) = val_opt {
                    return Ok(id.into());
                } else {
                    return Err(Error::from(format!("{}中没有找到值{},请检查修改", name, val)));
                }
            } else {
                let jv = convert(val, &fcol.field_type).or_else(|err| {
                    let msg = format!(
                        "值{:?}转化为字段{}类型{:?}出错:{}",
                        val,
                        name,
                        &fcol.field_type,
                        err
                    );
                    return Err(Error::from(msg));
                })?;
                if !fcol.is_null && jv.is_null() {
                    return Err(Error::from(format!("{}不能为空,请检查修改", name)));
                }
                return Ok(jv);
            }
        } else {
            return Err(
                Error::from(
                    format!("指定的excel列{}超出了数据的范围，请检查是否数据在Sheet1中，模板是否正确", col)
                )
            );
        }
    } else {
        if paras_vals.contains_key(name) {
            let val = Data::from(paras_vals[name].clone());
            let jv = convert(&val, &fcol.field_type).or_else(|err| {
                let msg = format!(
                    "值{:?}转化为字段{}类型{:?}出错:{}",
                    val,
                    name,
                    &fcol.field_type,
                    err
                );
                return Err(Error::from(msg));
            })?;
            return Ok(jv);
        } else {
            return Ok(Value::Null);
        }
    }
}
